using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using Mono.Data.SqliteClient;
using UnityEngine;
using System.Collections;
using System.Collections.Generic;

public class DatabaseConnectionExampleScript : MonoBehaviour {

	// Use this for initialization
	void Start () {
	}
	
	// Update is called once per frame
	void Update () {
	
	}
	
	public static Dictionary<string, string> LoadInventory() {
		Debug.Log("LoadInventory starting...");
		
		// default to an empty collection, in case we find no data
		Dictionary<string, string> gameItems = new Dictionary<string, string>(); 
		
		
		// Define a connection string which describes the db server we want to talk to.
		//
		// Normally this would be stashed in a config file, or dynamically built
		// with input from the user or a connection settings dialog, etc.
		// For the sake of this example, we're just hard-coding it.
		
		#region SQL Server
		//string cons = "Server=(local);Initial Catalog=atas;User=atas_user;Pwd=atas_pwd!";
		#endregion		
		string cons = "URI=file:" + Application.dataPath + "//dbexample.db";
		
		// Create a new connection to the db using that connection string
		SqliteConnection conn = null;
		try
		{		
			#region SQL Server
			//SqlConnection conn = new SqlConnection(cons);
			#endregion
			conn = new SqliteConnection(cons);
			
			// Open the connection
			conn.Open();
		}
		catch (System.Exception ex)
		{
			Debug.Log("The following error occurred establishing a db connection: " + ex.Message);
			return gameItems;
		}
		
		// Create a command object which we will use to issue a db query
		#region SQL Server
		//SqlCommand cmd = conn.CreateCommand();
		#endregion		
		DbCommand cmd = conn.CreateCommand();
		cmd.CommandType = CommandType.Text;
		cmd.CommandText =
			"SELECT item.item_name, it.item_type_description " +
		    "FROM item" +
			"   INNER JOIN item_type AS it " +
			"           ON it.item_type_cd = item.item_type_cd" ;
		
		Debug.Log ("Executing command: " + cmd.CommandText);
		
		// Execute the command on the server, and capture the results in a data reader object.
		// Then iterate through the data reader, and store the values in a List for use within our app.		
		//
		// Encapsulating the creation and use of the data reader in a "using" clause
		// ensures that the reader is automatically closed when the block exits.
		
		try
		{
			#region SQL Server
			//using (SqlDataReader reader = cmd.ExecuteReader()) {
			#endregion
			using (DbDataReader reader = cmd.ExecuteReader()) {
	
				if (reader.HasRows) {
					
					while (reader.Read()) {
						 gameItems.Add(reader.GetValue(0).ToString(), reader.GetValue(1).ToString());
					} // while
					
				} // if HasRows
				
			} // using
		}
		catch (System.Exception ex)
		{
			Debug.Log("The following error occurred reading from the db: " + ex.Message);
			return gameItems;
		}

		// Close the connection now that we're done with it
		//
		// Note that we could've surrounded this whole thing in an outer "using" block
		// which would have automatically closed the connection for us.
		conn.Close();
		
		
		// Return the data from the db, safely ensconsed in a Dictionary object
		Debug.Log("LoadInventory returning a collection of " + gameItems.Count + " item(s).");
		return gameItems;
	}
	
}
